package com.engc.common.service.impl;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.JdbcTemplate;

import com.engc.common.bean.SysUserInfo;
import com.engc.common.dao.DBExecuteException;
import com.engc.common.service.CommonService;
import com.engc.common.utils.BaseUtils;

public class CommonServiceImpl implements CommonService {

	@Resource
	private JdbcTemplate jdbcTemplate;

	private static final Log logger = LogFactory.getLog(CommonServiceImpl.class);

	public void recordUserOrg(String usercode, String fromOrgid, String toOrgid) throws DBExecuteException {
		if (StringUtils.isBlank(usercode) || StringUtils.isBlank(toOrgid)) {
			throw new DBExecuteException("Parameter [usercode,toOrgid]  can not be null");
		}
		String sql = null;
		if (StringUtils.isBlank(fromOrgid)) {
			// 新增人员,第一次产生关系
			sql = "insert into stb_user_org_his(id,usercode,orgid,jointime) values (SEQ_USERORG_HIS_ID.nextval,?,?,sysdate)";
			jdbcTemplate.update(sql,new Object[]{usercode,toOrgid});
		} else {
			// 变更组织机构关系
			sql = "update stb_user_org_his set lefttime=sysdate where usercode=? and orgid = ?";
			jdbcTemplate.update(sql,new Object[]{usercode,fromOrgid});

			sql = "insert into stb_user_org_his(id,usercode,orgid,jointime) values (SEQ_USERORG_HIS_ID.nextval,?,?,sysdate)";
			jdbcTemplate.update(sql,new Object[]{usercode,toOrgid});
		}
	}

	public void generateStudentIncreInfo(String usercode, String username, String orgid, Integer opertype,String xjzt,String sex) throws DBExecuteException {
		String sql = "insert into inc_student (id,name,orgid,opertype,mark,status) values(?,?,?,?,INC_STUDENT_MARK_ID.NEXTVAL,?)";
		logger.info("[SQL] >>>" + sql);
		jdbcTemplate.update(sql,new Object[]{usercode,username,orgid,opertype,xjzt});
	}
	
	public void generateTeacherIncreInfo(String usercode, String username, String orgid, Integer opertype, String subjectids,String sex) throws DBExecuteException {
		String sql = "insert into inc_teacher (id,name,orgid,opertype,mark,subjectid,sex) values(?,?,?,?,INC_TEACHER_MARK_ID.NEXTVAL,?,?)";
		jdbcTemplate.update(sql,new Object[]{usercode,username,orgid,opertype,subjectids,sex});
	}

	public void generateSchoolIncreInfo(String orgid, String orgname, String orgtype, Integer opertype,String areano) throws DBExecuteException {
		String sql = "insert into inc_school(orgid,orgname,orgtype,opertype,mark,areano)values(?,?,?,?,INC_SCHOOL_MARK_ID.NEXTVAL,?)";
		jdbcTemplate.update(sql, new Object[] { orgid, orgname, orgtype, opertype,areano });
	}


	/**
	 * 下发白名单 注销登记
	 * 
	 * @param user
	 * @param cardno
	 * @param db
	 * @throws Exception
	 */
	public void issureWhiteRole(Integer cardno, Integer cardstatus) throws Exception {
		// 白名单业务库
		String sql = "select count(cardno) from stb_whiteroll  where cardno =?";
		int count = jdbcTemplate.queryForInt(sql,new Object[]{cardno});
		// 白名单序列业务库
		sql = " select seq_whiterollmark.nextval from dual";
		int nextMark = jdbcTemplate.queryForInt(sql);
		if (count == 0) {
			// 新增
			sql = "INSERT INTO stb_whiteroll (CardStatus,CardNo,Mark) VALUES ((SELECT PosStatusID FROM stb_cardstatus WHERE EntityID = ?),?,?)";
			jdbcTemplate.update(sql, new Object[]{cardstatus,cardno,nextMark});
		} else {
			// 更新
			sql = "UPDATE stb_whiteroll SET CardStatus = (SELECT PosStatusID FROM stb_cardstatus WHERE EntityID = ?) ,Mark = ? where cardno = ?";
			jdbcTemplate.update(sql, new Object[]{cardstatus,nextMark,cardno});
		}
		// 全局变量表,业务库
		sql = " update STB_GLOBALLISTVERSION set versionno = ?  where versionid =3 ";
		jdbcTemplate.update(sql, new Object[]{nextMark});

	}

	public void generateGradeIncreInfo(String orgid,String fatherorgid, String orgname, String njzr, String njfzr, Integer opertype) throws DBExecuteException {
		String sql = "insert into INC_GRADE (orgid,fatherorgid,orgname,njzr,njfzr,opertype,mark)values(?,?,?,?,?,?,INC_GRADE_MARK_ID.nextval)";
		jdbcTemplate.update(sql, new Object[] { orgid,fatherorgid, orgname, njzr, njfzr, opertype });

	}

	public void generateClassIncreInfo(String orgid, String gradeorgid, String orgname, String bjzr, Integer opertype) throws DBExecuteException {
		String sql = "insert into INC_CLASS (orgid,gradeorgid,orgname,bjzr,opertype,mark)values(?,?,?,?,?,INC_CLASS_MARK_ID.nextval)";
		jdbcTemplate.update(sql, new Object[] { orgid, gradeorgid, orgname, bjzr, opertype });

	}

	public void generateSchoolYear(String id, String name, Integer opertype) throws DBExecuteException {
		String sql = "insert into INC_SCHOOL_YEAR(YEARID,YEARNAME,OPERTYPE,MARK)" + " VALUES(?,?,?,INC_SCHOOLYEAR_MARK_ID.nextval)";
		jdbcTemplate.update(sql, new Object[] { id, name, opertype });
	}

	public void generateTermInfo(String termid, String yearid, Integer skts, String isdefault, String begintime, String endtime, String dsz, Integer opertype)
			throws DBExecuteException {
		String sql = "insert into INC_SEMESTER(termid,yearid,skts,isdefault,begintime,endtime,dsz,opertype,mark)"
				+ "values(?,?,?,?,?,?,?,?,INC_TERM_MARK_ID.nextval)";
		jdbcTemplate.update(sql, new Object[] { termid, yearid, skts, isdefault, begintime, endtime, dsz, opertype });
	}

	public void generateSubjectIncreInfo(String id, String name, String sfks, String cjlx, String orgtype, Integer opertype) throws DBExecuteException {
		String sql = "insert into INC_SUBJECT (id,name,sfks,cjlx,opertype,orgtype,mark)" + "values(?,?,?,?,?,?,INC_SUBJECT_MARK_ID.nextval)";
		jdbcTemplate.update(sql, new Object[] { id, name, sfks, cjlx,opertype, orgtype });
	}

	public void generateDeptIncreInfo(String id, String name, String fatherid, Integer opertype) throws DBExecuteException {
		String sql = "insert into INC_DEPT(orgid,orgname,fuorgid,opertype,mark) " + "values(?,?,?,?,INC_DEPT_MARK_ID.nextval)";
		jdbcTemplate.update(sql, new Object[] { id, name, fatherid, opertype });
	}

	
	public String[] getCardAssistantInfo()  {
		// 用户密码（获取后需要+1000下发）
		String gsPwd = null;
		String yhPwd = null;
		String kA246 = null;
		String kB246 = null;

		gsPwd = String.valueOf(Integer.parseInt(getGlobalParamValue(30)));
		yhPwd = String.valueOf(Integer.parseInt(getGlobalParamValue(4)) + 1000);
		kA246 = getGlobalParamValue(14);
		kB246 = getGlobalParamValue(15);
		return new String[] { gsPwd, yhPwd, kA246, kB246 };
	}
	
	public String getGlobalParamValue(Integer paramId) {
		String sql = " select PARAMETERSTRING from STB_GLOBAL where PARAMETERID = ?";
		return jdbcTemplate.queryForObject(sql, new Integer[] { paramId }, String.class);
	}
	
	public void saveLogInfo(HttpServletRequest request,String opermodule,Integer logtype,String detail) throws DBExecuteException{
		SysUserInfo userInfo = BaseUtils.getCurrentUser(request);
		String usercode = userInfo!=null?userInfo.getUsercode():"";
		String orgid = userInfo!=null?userInfo.getOrgid():"";
		Long roleid = userInfo!=null?Long.parseLong(userInfo.getRoleid()):null;
		String sql = "insert into sys_log_info (id,usercode,roleid,opermodule,logtype,orgid,detail,opttime) values(SEQ_SYS_LOG.nextval,?,?,?,?,?,?,sysdate)";
		logger.info("[SQL] >>>" + sql);
		jdbcTemplate.update(sql,new Object[]{usercode,roleid,opermodule,logtype,orgid,detail});
	}
	
	/*public int queryInt(Connection con, String sql, Object... parameter) throws Exception {
		PreparedStatement ps = con.prepareStatement(sql);
		int i = 0;
		if (parameter != null) {
			for (Object param : parameter) {
				ps.setObject(++i, param);
			}
		}
		ResultSet rs = ps.executeQuery();
		int count = 0;
		while (rs.next()) {
			count = rs.getInt(1);
		}
		return count;
	}

	public int executeUpdate(Connection con, String sql, Object... parameter) throws Exception {
		logger.info("[SQL] >>>" + sql);
		PreparedStatement ps = con.prepareStatement(sql);

		int i = 0;
		if (parameter != null) {
			for (Object param : parameter) {
				ps.setObject(++i, param);
			}
		}

		return ps.executeUpdate();
	}
	
	public void recordUserOrgJDBC(Connection con, String usercode,
			String fromOrgid, String toOrgid) throws Exception {
		PreparedStatement ps = null;
		String sql = null;
		//新增关系
		sql = "insert into stb_user_org_his(id,usercode,orgid,jointime) values(SEQ_USERORG_HIS_ID.nextval,?,?,sysdate)";
		ps = con.prepareStatement(sql);
		ps.setString(1, usercode);
		ps.setString(2, toOrgid);
		ps.executeUpdate();
		if(StringUtils.isNotBlank(fromOrgid)){
			//不是第一次是更新时间
			sql = "update stb_user_org_his t set t.lefttime=sysdate where t.orgid = ? and t.usercode = ?";
			ps = con.prepareStatement(sql);
			ps.setString(1, fromOrgid);
			ps.setString(2, usercode);
			ps.executeUpdate();
		}
	}

	public void saveLogInfoJDBC(Connection con,HttpServletRequest request, String opermodule,
			Integer logtype, String detail) throws Exception {
		SysUserInfo userInfo = BaseUtils.getCurrentUser(request);
		String usercode = userInfo!=null?userInfo.getUsercode():"";
		String orgid = userInfo!=null?userInfo.getOrgid():"";
		Long roleid = userInfo!=null?Long.parseLong(userInfo.getRoleid()):null;
		String sql = "insert into sys_log_info (id,usercode,roleid,opermodule,logtype,orgid,detail,opttime)" +
		"values(SEQ_SYS_LOG.nextval,?,?,?,?,?,?,sysdate)";
		logger.info("[SQL] >>>" + sql);
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, usercode);
		ps.setLong(2, roleid);
		ps.setString(3, opermodule);
		ps.setInt(4, logtype);
		ps.setString(5, orgid);
		ps.setString(6, detail);
		ps.executeUpdate();
	}
	
	public void generateStudentIncreInfoJDBC(Connection con,String usercode, String username,
			String orgid, Integer opertype) throws Exception {
		String sql = "insert into inc_student(id,name,orgid,opertype,mark )values(?,?,?,?,INC_SCHOOL_MARK_ID.NEXTVAL)";
		logger.info("[SQL] >>>" + sql);
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, usercode);
		ps.setString(2, username);
		ps.setString(3, orgid);
		ps.setInt(4, opertype);
		ps.executeUpdate();
	}
	
	
	public void batchGenerateStudentIncInfoJDBC(Connection con,List<Map<String, Object>> infoLs)
			throws Exception {
		String sql = "insert into inc_student (id,name,orgid,opertype,mark) values(?,?,?,?,INC_STUDENT_MARK_ID.NEXTVAL)";
		PreparedStatement ps = con.prepareStatement(sql);
		for(Map<String, Object> map : infoLs){
			ps.setString(1, (String)map.get("usercode"));
			ps.setString(2, (String)map.get("username"));
			ps.setString(3, (String)map.get("orgid"));
			ps.setInt(4, Integer.valueOf((String)map.get("opertype")));
			ps.addBatch();
		}
		ps.executeBatch();
	}
	

	public Object queryForObject(Connection con, String sql, Object... parameter) throws Exception {
		PreparedStatement ps = con.prepareStatement(sql);
		int i = 0;
		if (parameter != null) {
			for (Object param : parameter) {
				ps.setObject(++i, param);
			}
		}
		ResultSet rs = ps.executeQuery();
		Object obj = null;
		while (rs.next()) {
			obj = rs.getObject(1);
		}
		return obj;
	}

	public Map<String, Object> queryForMap(Connection con, String sql, Object... parameter) throws Exception {
		Map<String, Object> map = new HashMap<String, Object>();
		PreparedStatement ps = con.prepareStatement(sql);
		int i = 0;
		if (parameter != null) {
			for (Object param : parameter) {
				ps.setObject(++i, param);
			}
		}
		ResultSet rs = ps.executeQuery();
		Map<String, Integer> metaDataMap = null;
		while (rs.next()) {
			metaDataMap = this.getMetaData(rs);
			map = this.setData(rs, metaDataMap);
		}
		return map;
	}

	public List<Map<String, Object>> queryForList(Connection con, String sql,
			Object... parameter) throws Exception {
		logger.info("[SQL] >>>" + sql);
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		PreparedStatement ps = con.prepareStatement(sql);
		int i = 0;
		if (parameter != null) {
			for (Object param : parameter) {
				ps.setObject(++i, param);
			}
		}
		ResultSet rs = ps.executeQuery();
		Map<String, Integer> metaDataMap = null;
		Map<String, Object> map = null;
		while (rs.next()) {
			metaDataMap = this.getMetaData(rs);
			map = this.setData(rs, metaDataMap);
			list.add(map);
		}
		return list;
	}

	*//**
	 * 根据传入的结果集返回结果集的元数据，以列名为键以列类型为值的map对象
	 * 
	 * @param rs
	 * @return
	 * @throws SQLException
	 *//*
	private Map<String, Integer> getMetaData(ResultSet rs) throws SQLException {
		Map<String, Integer> map = new HashMap<String, Integer>();
		ResultSetMetaData metaData = rs.getMetaData();
		int numberOfColumns = metaData.getColumnCount();
		for (int column = 0; column < numberOfColumns; column++) {
			String columnName = metaData.getColumnLabel(column + 1);
			int colunmType = metaData.getColumnType(column + 1);
			columnName = columnName.toLowerCase();
			map.put(columnName, colunmType);
		}
		return map;
	}

	*//**
	 * 将结果集封装为以列名存储的map对象
	 * 
	 * @param rs
	 * @param metaDataMap元数据集合
	 * @return
	 * @throws Exception
	 *//*
	private Map<String, Object> setData(ResultSet rs, Map<String, Integer> metaDataMap) throws Exception {
		Map<String, Object> map = new HashMap<String, Object>();
		for (String columnName : metaDataMap.keySet()) {
			Object object = rs.getObject(columnName);
			if (object == null) {
				map.put(columnName, null);
				continue;
			}
			map.put(columnName, object);
		}
		return map;
	}*/
	
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

}
